﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using ERP.DAL.Base;
using ERP.BusinessEntities.Base;
using ERP.BusinessEntities.Util;

namespace ERP.DAL.Util
{
    public class SQLDBUtil:IDBUtil
    {
        #region Query
        String strQ = @"SELECT
                    K_Table = FK.TABLE_NAME,
                    FK_Column = CU.COLUMN_NAME,
                    PK_Table = PK.TABLE_NAME,
                    PK_Column = PT.COLUMN_NAME,
                    Constraint_Name = C.CONSTRAINT_NAME
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
                    INNER JOIN (
                    SELECT i1.TABLE_NAME, i2.COLUMN_NAME
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
                    where FK.TABLE_NAME = 'ARPOSItems'
                    AND CU.COLUMN_NAME = 'FK_ICProductID'"; 
        #endregion

        public List<String> GetListTableInDatabase()
        {
            List<String> lstTableNames = new List<String>();
            DataSet ds =new DataSet();
            Host.db.LoadDataSet(CommandType.Text, "SELECT table_name AS Name FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE ='BASE TABLE'", ds, new String[] { "INFORMATION_SCHEMA.Tables" });
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dtRow in ds.Tables[0].Rows)
                {
                    if (dtRow["Name"] != null)
                    {
                        String strTableName = dtRow["Name"].ToString();
                        lstTableNames.Add(strTableName);
                    }
                }
            }


            return lstTableNames;
        }

        public List<String> GetListColumnNameOfTable(String strTable)
        {
            List<String> lstColumnNames = new List<String>();
            String strQuery = String.Format("SELECT COLUMN_NAME AS ColumnName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", strTable);
            DataSet ds = new DataSet();
            Host.db.LoadDataSet(CommandType.Text, strQuery, ds, new String[] { "INFORMATION_SCHEMA.COLUMNS" });
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dtRow in ds.Tables[0].Rows)
                {
                    if (dtRow["ColumnName"] != null)
                    {
                        String strColumnName = dtRow["ColumnName"].ToString();
                        lstColumnNames.Add(strColumnName);
                    }
                }
            }
            return lstColumnNames;
        }

        public bool CheckColumnIsExist(String strTable, String strColumn)
        {
            String strQuery = String.Format("SELECT COLUMN_NAME AS ColumnName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", strTable, strColumn);
            DataSet ds = new DataSet();
            Host.db.LoadDataSet(CommandType.Text, strQuery, ds, new String[] { "INFORMATION_SCHEMA.COLUMNS" });
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }

        public String GetPrimaryKeyOnTable(String strTable)
        {
            String strPrimeryKey = string.Empty;
            String strQuery = String.Format(@"select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
                                                inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
                                                on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                                                where a.table_name = '{0}' and constraint_type = 'Primary key'", strTable);
            strPrimeryKey = (String)Host.db.ExecuteScalar(CommandType.Text, strQuery);
            
            return strPrimeryKey;
        }

        public List<String> GetListForeignKeyOnTable(String strTable)
        {
            List<String> lstForeignColumnNames = new List<String>();
            String strQuery = String.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = '{0}'", strTable);
            DataSet ds = new DataSet();
            Host.db.LoadDataSet(CommandType.Text, strQuery, ds, new String[] { "INFORMATION_SCHEMA.TABLE_CONSTRAINTS" });
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dtRow in ds.Tables[0].Rows)
                {
                    if (dtRow["CONSTRAINT_NAME"] != null)
                    {
                        String strForeignKeyName = dtRow["Name"].ToString();
                        lstForeignColumnNames.Add(strForeignKeyName);
                    }
                }
            }

            return lstForeignColumnNames;
        }

        public KeyValuePair<String, int> GetTypeOfColumnOnTable(String strTable, String strColumn)
        {
            String strType = String.Empty;
            int Lenght = 0;
            String strQuery = String.Format("SELECT Data_Type, CHARACTER_MAXIMUM_LENGTH as Length, IS_NULLABLE as AllowNull FROM information_schema.columns WHERE table_name = '{0}' AND COLUMN_NAME = '{1}'", strTable, strColumn);
            DataSet ds = new DataSet();
            Host.db.LoadDataSet(CommandType.Text, strQuery, ds, new String[] { "information_schema.columns" });
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow dtRow = ds.Tables[0].Rows[0];
                if (dtRow["Data_Type"] != null)
                {
                    strType = dtRow["data_type"].ToString();
                }
                if (dtRow["Length"] != null)
                {
                    try
                    {
                        Lenght = int.Parse(dtRow["Length"].ToString());
                        if (Lenght > 8000)
                            Lenght = 0;
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    
                }
                if(dtRow["AllowNull"] != null)
                {
                    try
                    {
                        String str = dtRow["Length"].ToString();
                        if(String.IsNullOrEmpty(str) == false && str.ToUpper().Equals("NO"))
                            Lenght = -1;
                    }
                    catch (System.Exception ex)
                    {

                    }
                }

            }

            return new KeyValuePair<String, int>(strType, Lenght);
        }

        public String GetTableNameFromBusinessObject(BaseBusinessInfo obj)
        {
            String strTableName = String.Empty;
            Type objType = obj.GetType();
            strTableName = objType.Name;
            strTableName = strTableName.Substring(0, strTableName.Length - 4);
            return strTableName;
        }

        public void CreateStroredProcedure(String strStoreProcedure)
        {
            try
            {
                Host.db.ExecuteNonQuery(CommandType.Text, strStoreProcedure);
            }
            catch (Exception ex)
            {
                strStoreProcedure = strStoreProcedure.Replace("ALTER", "CREATE");
                Host.db.ExecuteNonQuery(CommandType.Text, strStoreProcedure);
            }

        }

        public String GetPrimaryTableFromForeignTableAndRelation(String strForeignTable, String strColumnForeign)
        {
            String strQuery = String.Format(@"SELECT
                                                PK_Table = PK.TABLE_NAME
                                                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
                                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
                                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
                                                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
                                                INNER JOIN (
                                                SELECT i1.TABLE_NAME, i2.COLUMN_NAME
                                                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                                                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                                                WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                                                ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
                                                where FK.TABLE_NAME = '{0}'
                                                AND CU.COLUMN_NAME = '{1}'", strForeignTable, strColumnForeign);
            object obj = Host.db.ExecuteScalar(CommandType.Text, strQuery);
            if(obj != null)
                return (String)obj;

            return string.Empty;

        }

        public int GetPrimaryKeyOfBusinessObject(BaseBusinessInfo obj)
        {
            String TableName = Host.sqlDbUtil.GetTableNameFromBusinessObject(obj);
            String strMainPrimaryKey = Host.sqlDbUtil.GetPrimaryKeyOnTable(TableName);

            return (int)DynamicObject.GetProperty(obj, strMainPrimaryKey);
        }

    }
}
